package br.com.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.Conexao.conexao;
import br.com.Modelo.Endereco;
import br.com.Modelo.Pagina;
import br.com.Modelo.UsuarioCliente;

public class EnderecoDao {
	
Connection connection;
	
	public EnderecoDao(){
		
		this.connection = new conexao().getConnection();
		
	}
	
	public void adiciona(Endereco end) {
    	
    	//if(isLoginEqual(usuario)==false){
	    String sql = "insert into tb_endereco " +
	            "(cep,cidade,bairro,numero,idPagina,count,rua,telefone)" +
	            " values (?,?,?,?,?,?,?,?)";

	    try {
	        // prepared statement para inserção
	        PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);

	        // seta os valores

	        stmt.setString(1,end.getCep());
	        stmt.setString(2,end.getCidade());
	        stmt.setString(3,end.getBairro());
	        stmt.setInt(4,end.getNumero());
	        stmt.setInt(5,end.getIdPagina());
	        stmt.setInt(6,end.getCount()); 
	        stmt.setString(7,end.getRua());
	        stmt.setString(8,end.getTelefone());
	     

	        // executa
	        stmt.execute();
	        System.out.print("Pagina add com sucesso");
	        stmt.close();
	    } catch (SQLException e) {
	        throw new RuntimeException(e);
	    }
    
	}
	
	public List<Endereco> getEnds(int id_pagina) {
		try {	
		List<Endereco> ends = new ArrayList<Endereco>();
		PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select * from tb_endereco where idPagina='"+id_pagina+"'");
		ResultSet rs= stmt.executeQuery();
		
			while(rs.next()){
			    Endereco end = new Endereco();
			    end.setId_end(rs.getLong("id_end"));
			    end.setCep(rs.getString("cep"));
			    end.setCidade(rs.getString("cidade"));
			    end.setBairro(rs.getString("bairro"));			    
			    end.setNumero(rs.getInt("numero"));
			    end.setIdPagina(rs.getInt("idPagina"));
			    end.setCount(rs.getInt("count"));
			    end.setRua(rs.getString("rua"));
			    end.setTelefone(rs.getString("telefone"));
		
			      ends.add(end);
		        }
			rs.close();
			stmt.close();
			connection.close();
		     return ends;
		 } catch (SQLException e) {
		         throw new RuntimeException(e);
		  }
	    }
	
	public Endereco getEnd(int id_end) {
		try {	
		    Endereco end = new Endereco();
		PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select * from tb_endereco where id_end='"+id_end+"'");
		ResultSet rs= stmt.executeQuery();
		
			while(rs.next()){
		
			    end.setId_end(rs.getLong("id_end"));
			    end.setCep(rs.getString("cep"));
			    end.setCidade(rs.getString("cidade"));
			    end.setBairro(rs.getString("bairro"));			    
			    end.setNumero(rs.getInt("numero"));
			    end.setIdPagina(rs.getInt("idPagina"));
			    end.setCount(rs.getInt("count"));
			    end.setRua(rs.getString("rua"));
			    end.setTelefone(rs.getString("telefone"));
		
			   
		        }
			rs.close();
			stmt.close();
			connection.close();
		     return end;
		 } catch (SQLException e) {
		         throw new RuntimeException(e);
		  }
	    }

	
	public void updateEndereco(Endereco end){
		
		   String sql = "UPDATE tb_endereco SET cep=?, cidade=?, bairro=?, numero=?, rua=?, telefone=? WHERE id_end=?";
		
		    try {
		        // prepared statement para inserção
		        PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);

		        // seta os valores

		        stmt.setString(1,end.getCep());
		        stmt.setString(2,end.getCidade());
		        stmt.setString(3,end.getBairro());
		        stmt.setInt(4,end.getNumero());	
		        stmt.setString(5,end.getRua());
		        stmt.setString(6,end.getTelefone());
		        stmt.setLong(7,end.getId_end());
		     

		        // executa
		        stmt.executeUpdate();
		        System.out.print("Pagina add com sucesso");
		        stmt.close();
		    } catch (SQLException e) {
		        throw new RuntimeException(e);
		    }
		
	}
	
	public List<Endereco> getbyEnd(String perfil_pagina) {
		try {	
		List<Endereco> ends = new ArrayList<Endereco>();
		PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select * from tb_endereco a, tb_pagina b where a.idPagina=b.id_pagina and  b.pagina='"+perfil_pagina+"'");
		ResultSet rs= stmt.executeQuery();
		
			while(rs.next()){
			    Endereco end = new Endereco();
			    end.setId_end(rs.getLong("id_end"));
			    end.setCep(rs.getString("cep"));
			    end.setCidade(rs.getString("cidade"));
			    end.setBairro(rs.getString("bairro"));			    
			    end.setNumero(rs.getInt("numero"));
			    end.setIdPagina(rs.getInt("idPagina"));
			    end.setCount(rs.getInt("count"));
			    end.setRua(rs.getString("rua"));
			    end.setTelefone(rs.getString("telefone"));
		
			      ends.add(end);
		        }
			rs.close();
			stmt.close();
			connection.close();
		     return ends;
		 } catch (SQLException e) {
		         throw new RuntimeException(e);
		  }
	    }
	
	 public void removerEndereco(long id_end){
		 
			//if(isLoginEqual(usuario)==false){
		    String sql = "delete from tb_endereco where id_end='"+id_end+"'";
		           
		

		    try {
		        // prepared statement para inserção
		        PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);

		        // seta os valores

		     
	

		        // executa
		        stmt.execute();
		        System.out.print("Pagina add com sucesso");
		        stmt.close();
		        connection.close();
		    } catch (SQLException e) {
		        throw new RuntimeException(e);
		    }
		 
		 
		 
		 
	 }
}
